package com.itogis.interceptors.autoExportExcel;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

public class ExcelReportManage {

	public static ResultObject selectData(String sql) throws Exception {
		Connection conn = getConnectionTest();
		ResultObject returnValue = new ResultObject();
		ResultSet rs = null;
		ResultSet rs1 = null;
		PreparedStatement ps = null;
		try {	
			returnValue.setSql(sql);
			ps = conn.prepareStatement("select count(*) num from (" + sql + ")");
			rs1 = ps.executeQuery();
			rs1.next();
			returnValue.setRowCount(rs1.getInt(1));
			if(rs1.getInt(1)<=1)
			{		
				rs=selectDataList(sql);
				rs.next();
				returnValue.setResult(rs);
			}
			else
			{
				returnValue.setResult(null);
			}	
			return returnValue;
		} catch (Exception ex) {
			throw ex;
		} finally {
			try { 				
				 if (ps != null) {
				 ps.close();
				 ps = null;
				 }				
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
	}
	
	public static ResultSet selectDataList(String sql) throws Exception {
		Connection conn = getConnectionTest();		
		ResultSet rs = null;
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();	
			return rs;
		} catch (Exception ex) {
			throw ex;
		} finally {
			try {				
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
	}
	public static Connection getConnectionTest() throws Exception {
		Connection conn = null;
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			// conn = DriverManager.getConnection(
			// "jdbc:oracle:thin:@135.224.83.170:1521:exdb1", "xjdw",
			// "xjdw$123");
			conn = DriverManager.getConnection(
					"jdbc:oracle:thin:@127.0.0.1:1521:wbdb", "pro1206",
					"pro1206");

		} catch (Exception e) {
			throw new Exception(e);
		}
		return conn;
	}
	

	public static String exportExcelReportByDemo(String outPath,String demoFileName,int demoColCount,int demoRowCount,String outFileName,String SqlParameters) throws Exception {
		Map map = new HashMap();
		Map mapListData = new HashMap();
		String file = "";
		System.out.println("***********************  a 7");
		String demoPath="c:\\";	

		int allColCount=demoColCount;
		int allRowCount=demoRowCount;
		System.out.println("***********************  a 8");
		String demoFilePath = demoPath+demoFileName;
		System.out.println("***********************  a 9");
		Date date = new Date();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmSS");
		// 格式化日期(产生文件名)
		System.out.println("***********************  a 10");
		String strDate = sdf.format(date);
		outFileName=outFileName+strDate+".xls";
		System.out.println("***********************  a 11");
		String outFilePath = outPath+outFileName;
		System.out.println("***********************  a 12");
	
		try {			
			File tFile = new File(demoFilePath);
			File outFile = new File(outFilePath);
			FileOutputStream os = new FileOutputStream(outFile);			
			Workbook tBook = Workbook.getWorkbook(tFile);			
			WritableWorkbook wbook = Workbook.createWorkbook(os, tBook);			
			WritableSheet wsheet = wbook.getSheet(0);
			boolean rowCopy = false;
			int listRow = 0;
			String lsName="";
			for (int row = 0; row < allRowCount; row++) {
				for (int col = 0; col < allColCount; col++) {					 
					file = wsheet.getCell(col, row).getContents();					
					if (col == 0 && row == 0) {
						String[] sql = file.split(";");
						for (int r = 0; r < sql.length; r++) {
							ResultObject rs1 = selectData(sql[r].split("=")[1]);
							allRowCount=allRowCount+rs1.getRowCount();
							map.put(sql[r].split("=")[0], rs1);
						}
					}					
					if (file.toLowerCase().indexOf("&ls") == 0 && !rowCopy && !lsName.equals(file.substring(1, file.length() - 1).split("\\.")[0])) {
						
						lsName=file.substring(1, file.length() - 1).split("\\.")[0];
						listRow = row;
						
						
						mapListData.put(listRow+","+col, map.get(
								file.substring(1, file.length() - 1).split(
								"\\.")[0]));
						
						
						int rowc = row;
						listRow = row;
						ResultObject cc =(ResultObject) map.get(map.get(file.substring(1,
								file.length() - 1).split("\\.")[0]));
						for (int g = 0; g < cc.rowCount; g++) {
							wsheet.insertRow(rowc);
							for (int col2 = 0; col2 < allColCount; col2++) {
								WritableCell cell = wsheet.getWritableCell(
										col2, rowc + 1).copyTo(col2, rowc);
								wsheet.addCell(cell);
							}
							rowCopy = true;
						}
						rowCopy = false;
						wsheet.removeRow(rowc);
						rowc = row;
					}
					
					if (file.toLowerCase().indexOf("&rs") != -1)
					{						
						String content=wsheet.getCell(col,row).getContents();						
						String[] contentArray = content.split("&");
						for(int w=0;w<contentArray.length;w++)
						{
							if(w%2==1)
							{							 
							  String rsName=contentArray[w].split("\\.")[0];
							  String rsField=contentArray[w].split("\\.")[1];
							  ResultObject cc =(ResultObject) map.get(rsName);
							  ResultSet rs3 = cc.getResult();							 
							  content=content.replaceAll("&"+contentArray[w]+"&", rs3.getString(rsField.toUpperCase()));
							}
						}						
						jxl.format.CellFormat cf = wsheet
						.getCell(col,row).getCellFormat();
						Label label = new Label(col,row, content);
						label.setCellFormat(cf);
						wsheet.addCell(label);
					}	
				}
			}
            //***********************************
	
			
			
			
			Iterator it = mapListData.entrySet().iterator();  
			while (it.hasNext()) {			
				        Map.Entry entry = (Map.Entry) it.next();  
				 
				        String key = (String) entry.getKey();  
				 
				       Object mapValue = entry.getValue();  
				ResultObject rsw=(ResultObject) mapValue;
				ResultSet rsList = selectDataList(rsw.getSql());
				listRow=Integer.valueOf(key.split(",")[0]);
				int colss=Integer.valueOf(key.split(",")[1]);
				while (rsList.next()) {
					listRow=listRow + 1;					
					for (int col2 = colss; col2 < allColCount; col2++) {
						jxl.format.CellFormat cf = wsheet
								.getCell(col2, listRow-1).getCellFormat();// ��ȡ��һ��Ԫ��ĸ�ʽ
						String value = wsheet.getCell(col2, listRow-1)
								.getContents();						
						if (value.toLowerCase().indexOf("&ls") != -1)
						{
							String content=wsheet.getCell(col2,listRow-1).getContents();							
							String[] contentArray = content.split("&");
							for(int w=0;w<contentArray.length;w++)
							{
								if(w%2==1)
								{								  
								  String rsName=contentArray[w].split("\\.")[0];
								  String rsField=contentArray[w].split("\\.")[1];
								  content=content.replaceAll("&"+contentArray[w]+"&", rsList.getString(rsField.toUpperCase()));
								}
							}							
							Label label = new Label(col2,listRow-1, content);
							label.setCellFormat(cf);
							wsheet.addCell(label);
						}	
						else 
						{
							Label label = new Label(col2, listRow-1, "");
							label.setCellFormat(cf);
							wsheet.addCell(label);
						}
					}
				}
			}	
            //************************************			
		    wsheet.removeRow(0);
			wbook.write();
			wbook.close();
			tBook.close();			
			os.close();
		} catch (Exception e) {
			System.out.println(e.getMessage());
			e.printStackTrace();
		}
		return outFileName;
	}
	public static void main(String[] args) {
		
	}

}
